/**
 * 
 */
package net.dfrz.java1305.team4.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.dfrz.java1305.team4.domain.Category;
import net.dfrz.java1305.team4.utils.DBUtils;

/**
 * @author acer
 *
 */
public class CategoryDaoJDBCImpl implements CategoryDao {

	private static final String SQL_ADD="insert into t_category values(ctg_seq.nextval,?,?,?)";
	private static final String SQL_LOAD_ALL="select * from t_category";
	private static final String SQL_GET_BY_ID="select * from t_category where ctg_id=?";
	private static final String SQL_DELETE_BY_ID="delete from t_category where ctg_id=?";
	private static final String SQL_UPDATE="update t_category set ctg_pid=?,ctg_name=?,ctg_memo=? where ctg_id=?";
	
	private Connection connection=null;
	private PreparedStatement pdst=null;
	private ResultSet resultSet=null;
	
	@Override
	public void addCategory(Category category) {
		
		this.connection=DBUtils.getInstance().getConnection();
		
		try {
			this.pdst=this.connection.prepareStatement(SQL_ADD);
			
			int categoryPid=category.getCategoryId();
			this.pdst.setInt(1, categoryPid==-1?null:categoryPid);
			this.pdst.setString(2, category.getCategoryName());
			this.pdst.setString(3, category.getCategoryMemo());
			
			this.pdst.executeUpdate();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().release(connection, pdst, null);
		}
		
	}

	@Override
	public List<Category> loadAll() {
		
		List<Category> categoryList=new ArrayList<Category>();
		Category category=null;
		
		this.connection=DBUtils.getInstance().getConnection();
		
		try {
			this.pdst=this.connection.prepareStatement(SQL_LOAD_ALL);
			
			this.resultSet=this.pdst.executeQuery();
			
			while(this.resultSet.next()){
				
				category=new Category();
				
				category.setCategoryId(this.resultSet.getInt("ctg_id"));
				category.setCategoryPid(this.resultSet.getInt("ctg_pid"));
				category.setCategoryName(this.resultSet.getString("ctg_name"));
				category.setCategoryMemo(this.resultSet.getString("ctg_memo"));
				
				categoryList.add(category);
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().release(connection, pdst, resultSet);
		}
		
		return categoryList;
	}

	@Override
	public Category getCategoryById(Integer categoryId) {
		
		Category category=null;
		
		this.connection=DBUtils.getInstance().getConnection();
		
		try {
			this.pdst=this.connection.prepareStatement(SQL_GET_BY_ID);
			
			this.pdst.setInt(1, categoryId);
			
			this.resultSet=this.pdst.executeQuery();
			
			if(this.resultSet.next()){
				
				category=new Category();
				
				category.setCategoryId(this.resultSet.getInt("ctg_id"));
				category.setCategoryPid(this.resultSet.getInt("ctg_pid"));
				category.setCategoryName(this.resultSet.getString("ctg_name"));
				category.setCategoryMemo(this.resultSet.getString("ctg_memo"));
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().release(connection, pdst, resultSet);
		}
		
		return category;
	}

	@Override
	public void deleteCategory(Integer categoryId) {
		
		this.connection=DBUtils.getInstance().getConnection();
		
		try {
			this.pdst=this.connection.prepareStatement(SQL_DELETE_BY_ID);
			
			this.pdst.setInt(1, categoryId);
			
			this.pdst.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().release(connection, pdst, null);
		}
	}

	@Override
	public void updateCategory(Category category) {
		
		this.connection=DBUtils.getInstance().getConnection();
		
		try {
			this.pdst=this.connection.prepareStatement(SQL_UPDATE);
			
			this.pdst.setInt(1, category.getCategoryPid());
			this.pdst.setString(2, category.getCategoryName());
			this.pdst.setString(3, category.getCategoryMemo());
			this.pdst.setInt(4, category.getCategoryId());
			
			this.pdst.executeUpdate();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().release(connection, pdst, null);
		}
		
	}

}
